


/**
	Author	    : Rohan
	Date	    : August 10th, 2004
	Description : This proc accepts a list of process step Id's seperated by comma, 
		      and returns them as set of rows each row containing an Process Step Id
	Usage       : 
*/


ALTER  function fn_get_process_steps_in_string
( 
@process_steps_str varchar(2000) 
)
returns @fn_result Table ( ProcessStep_Id int )
as
begin

declare @process_step_id int
declare @str_index int
declare @temp_str varchar(10)
declare @next_char char(1)
declare @ps_count int

--inserting the process steps passed as parameter into a temp table
set @str_index = 1
set @temp_str = ''

while( @str_index <= len( @process_steps_str ) )
begin
	set @next_char = substring( @process_steps_str, @str_index, 1 )
	if( @next_char = ',' )
	begin
		if( @temp_str != '' )
		begin
			set @process_step_id = convert( bigint, @temp_str )
			select @ps_count = count(*) from @fn_result where ProcessStep_ID = @process_step_id
			if( @ps_count = 0 )
			begin
				insert into @fn_result( ProcessStep_ID )  values( @process_step_id )
			end
			set @temp_str = ''
		end
	end
	else if( @next_char != ' ' )
	begin
		set @temp_str = @temp_str + @next_char
	end
	set @str_index = @str_index + 1
end

--inserting the last process step in the string
if( @temp_str != '' )
begin
	insert into @fn_result( ProcessStep_ID )  values( convert( bigint, @temp_str ) )
	set @temp_str = ''
end

return
end


